ASYNC_IO_COMPLETION Wait Type In SQL Server

The ASYNC_IO_COMPLETION wait type is commonly encountered by a database administrator. It occurs every time SQL Server performs a file-related action at storage level and waits for it to complete. As the wait type is related to storage, you might encounter more often when a backup happens. However, if you encounter the ASYNC_IO_COMPLETION wait type.

What is the ASYNC_IO_COMPLETION Wait Type?

The ASYNC_IO_COMPLETION wait type occurs when a task is waiting for storage-related action to complete. In general, this wait type is more seen while a backup is happening in your server. The SQL Server monitors the signal from the storage subsystem for faster completion. In case of a delay from the storage side, the ASYNC_IO_COMPLETION wait type occurs. To be simpler, the faster your storage subsystem, the lower you see the ASYNC_IO_COMPLETION wait type.

If you are seeing this wait type in your server, it means that your SQL Server task is communicating with the storage subsystem and there should not be any matter of concern. It generally flags the wait type when a database creation or a database backup operation takes place. You should take the wait type into consideration when the wait times are higher than the baseline measurements.  

Sys.dm_os_wait_stats

You can check the ASYNC_IO_COMPLETION wait type using the sys.dm_os_wait_stats DMV.

Before running the DMV, lets clear the information from the sys.dm_os_wait_stats DMV using a simple DBCC command.

use master
go
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

I will now run a full backup in my server. As soon as I execute the full backup, the information gets loaded into the DMV.

select * from sys.dm_os_wait_stats where wait_type='ASYNC_IO_COMPLETION'

How to Lower ASYNC_IO_COMPLETION Wait Type?

In most cases, high ASYNC_IO_COMPLETION wait is due to a database backup. To find if the wait type was because of a backup, then query the DMV with other backup waits.

select * from sys.dm_os_wait_stats  where wait_type
in ('ASYNC_IO_COMPLETION', 'BACKUPIO','BACKUPBUFFER') 

You can also try to lower the ASYNCH_IO_COMPLETION waits by configuring instant file initialization. Instant file initialization is a feature introduced by Microsoft for Windows 2003 to speed up the disk space allocation.

Bottomline

The ASYNC_IO_COMPLETION wait type is triggered by actions related to the storage subsystem within your SQL Server instance, such as database backups and new database creation. While these waits are generally normal, unusually high wait times can suggest storage issues. Before involving your storage administrator, confirm there is a true performance issue. One way to do this is by checking storage latency, as high latency can increase ASYNC_IO_COMPLETION wait times. Also, verify if the increased wait times coincide with database backups. Enabling instant file initialization by adding your SQL Server service account to the Perfmon volume maintenance tasks local policy can help reduce ASYNC_IO_COMPLETION wait times.

You may also refer:

BACKUPBUFFER Wait Type In SQL Server

How A Forced Failover Can Cause Data Loss In SQL Server?

I was working with one my clients in Always-On and unfortunately in the process of fixing an error, one of the members in our team forcefully failed over Always-On availability group from one instance to another. There is a need to understand the risks associated with force failover as it can lead to data loss.

This occurs because the target replica may be unable to communicate with the primary replica, making it impossible to ensure that the databases are synchronized. Figure below illustrates how a forced failover can cause data loss on the primary replica and how this loss can extend to a secondary replica.

1. In the previous example, the primary replica's last hardened LSN is 100 before it goes offline, while the asynchronous secondary replica's LSN is 50.

2. After a forced failover, the secondary replica becomes the new primary and sets its last hardened LSN to 50.

3. When the old primary comes back online, its synchronization status is suspended.

4. If synchronization is resumed, the old primary will sync with the new primary, sending its last hardened LSN as 100. Upon finding the new primary's last hardened LSN is 50, it will roll back its transaction log to LSN 50 and start accepting transaction log blocks from the new primary from that point onward. Thus, data loss is propagated from the primary to the secondary replica if synchronization is resumed.

Refer the related articles -
Data Synchronization Modes In Always On In SQL Server
How Does A Synchronous And Asynchronous Secondary Replica Resynchronize With The Primary Replica?

Difference Between sp_who and sp_who2 In SQL Server

The sp_who and sp_who2 are the two most used system stored procedure to get information on users, sessions, and processes of an instance in an SQL Server. In a production environment, it becomes an important aspect for a DBA to get the SQL Server processes running. Though both systems' stored procedures are used to get the users, sessions, and processes information, there are some key differences.

sp_who

• The sp_who has limited columns to show. The results are all related to the processes running in an SQL Server instance.
•  The sp_who is official and documented by Microsoft.  
• The sp_who is supported by Microsoft.

Sp_who2

• The sp_who2 has more columns to show than the sp_who. Like sp_who, the results are all related to the processes running in an SQL Server instance.
•  The sp_who2 is not official and is documented by Microsoft.
• The sp_who2 is not supported by Microsoft. 

Bottomline

Both sp_who and sp_who2 stored procedures are handy for a DBA to get the process information in SQL Server. For less column information you can prefer to go with sp_who whereas for more column information you can prefer to go with sp_who2. You can judge which stored procedure to use based on your requirement.